﻿using Dapper;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace FFSupplier.Common.Helper
{
    public class SQLDBHELPER
    {
        private string connectionstring;
        private SqlConnection connection;
        private SqlTransaction transaction;
        public SQLDBHELPER()//构造函数
        {
            connectionstring = ConfigurationManager.ConnectionStrings["CrmDB_Connection"].ConnectionString;
            connection = new SqlConnection(connectionstring);
        }
        /// <summary>   
        /// 执行SQL语句并返回数据表   
        /// </summary>   
        /// <param name="Sqlstr">SQL语句</param>   
        /// <returns></returns>   
        public DataTable ExecuteDt(String Sqlstr)
        {
            using (SqlConnection conn = new SqlConnection(connectionstring))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                DataTable dt = new DataTable();
                conn.Open();
                da.Fill(dt);
                conn.Close();
                return dt;
            }
        }
        /// <summary>   
        /// 事务执行SQL  
        /// </summary>   
        /// <param name="Sqlstr">SQL语句</param>   
        /// <returns></returns>   
        public DataTable ExecuteDtTrans(String Sqlstr)
        {
            using (SqlConnection conn = new SqlConnection(connectionstring))
            {
                SqlDataAdapter da = new SqlDataAdapter(Sqlstr, conn);
                DataTable dt = new DataTable();
                conn.Open();
                System.Data.SqlClient.SqlTransaction trans = conn.BeginTransaction();

                da.Fill(dt);
                conn.Close();
                return dt;
            }
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="spName">存储过程名称</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>返回对应Model</returns>
        public T ExecStoredProcedure<T>(string spName, Hashtable commandParameters)
        {
            T data;
            try
            {
                using (connection)
                {
                    connection.Open();
                    DynamicParameters parm = new DynamicParameters();
                    if (commandParameters != null)
                    {
                        foreach (DictionaryEntry para in commandParameters)
                        {
                            parm.Add(para.Key.ToString(), para.Value);
                        }
                    }
                    data = connection.Query<T>(spName, parm, commandType: CommandType.StoredProcedure).FirstOrDefault();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                connection.Close();
            }
            return data;
        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="spName">存储过程名称</param>
        /// <param name="commandParameters">参数</param>
        /// <returns>返回列表</returns>
        public List<T> ExecStoredProcedureList<T>(string spName, Hashtable commandParameters)
        {
            List<T> data;
            try
            {
                using (connection)
                {
                    connection.Open();
                    DynamicParameters parm = new DynamicParameters();
                    if (commandParameters != null)
                    {
                        foreach (DictionaryEntry para in commandParameters)
                        {
                            parm.Add(para.Key.ToString(), para.Value);
                        }
                    }
                    data = connection.Query<T>(spName, parm, commandType: CommandType.StoredProcedure).AsList();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                connection.Close();
            }
            return data;
        }
        /// <summary>
        /// 分页-执行存储过程
        /// </summary>
        /// <param name="pageInfo">分页参数</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="commandParameters">查询参数</param>
        /// <returns></returns>
        public List<T> ExecStoredProcedurePage<T>(PageInfo pageInfo, string spName, Hashtable commandParameters)
        {

            List<T> data;
            try
            {
                using (connection)
                {
                    connection.Open();
                    DynamicParameters parm = new DynamicParameters();
                    if (pageInfo != null)
                    {
                        parm.Add("pageSize", pageInfo.PageSize);
                        parm.Add("pageNumber", pageInfo.PageNumber);
                        parm.Add("orderString", pageInfo.OrderString);
                        //parm.Add("recordTotal", 0, DbType.Int32, ParameterDirection.Output);
                    }
                    if (commandParameters != null)
                    {
                        foreach (DictionaryEntry para in commandParameters)
                        {
                            parm.Add(para.Key.ToString(), para.Value);
                        }
                    }
                    data = connection.Query<T>(spName, parm, commandType: CommandType.StoredProcedure).AsList();

                    //int recordTotal = parm.Get<int>("@recordTotal");//返回总条数

                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                connection.Close();
            }
            return data;
        }

        /// <summary>
        /// 分页-执行存储过程  客户
        /// </summary>
        /// <param name="pageInfo">分页参数</param>
        /// <param name="spName">存储过程名称</param>
        /// <param name="commandParameters">查询参数</param>
        /// <returns></returns>
        //public AccountListResponse ExecStoredProcedurePage2<T>(PageInfo pageInfo, string spName, Hashtable commandParameters)
        //{

        //    AccountListResponse data = new AccountListResponse();
        //    try
        //    {
        //        using (connection)
        //        {
        //            connection.Open();
        //            DynamicParameters parm = new DynamicParameters();
        //            if (pageInfo != null)
        //            {
        //                parm.Add("pageSize", pageInfo.PageSize);
        //                parm.Add("pageNumber", pageInfo.PageNumber);
        //                parm.Add("orderString", pageInfo.OrderString);
        //                parm.Add("recordTotal", 0, DbType.Int32, ParameterDirection.Output);
        //            }
        //            if (commandParameters != null)
        //            {
        //                foreach (DictionaryEntry para in commandParameters)
        //                {
        //                    parm.Add(para.Key.ToString(), para.Value);
        //                }
        //            }
        //            data.List = connection.Query<AccountListReturn>(spName, parm, commandType: CommandType.StoredProcedure).AsList();

        //            data.recordTotal = parm.Get<int>("@recordTotal");//返回总条数

        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        throw new Exception(e.Message);
        //    }
        //    finally
        //    {
        //        connection.Close();
        //    }
        //    return data;
        //}
        ///// <summary>
        ///// 分页-执行存储过程  线索
        ///// </summary>
        ///// <param name="pageInfo">分页参数</param>
        ///// <param name="spName">存储过程名称</param>
        ///// <param name="commandParameters">查询参数</param>
        ///// <returns></returns>
        //public LeadListResponse ExecStoredProcedurePage3<T>(PageInfo pageInfo, string spName, Hashtable commandParameters)
        //{

        //    LeadListResponse data = new LeadListResponse();
        //    try
        //    {
        //        using (connection)
        //        {
        //            connection.Open();
        //            DynamicParameters parm = new DynamicParameters();
        //            if (pageInfo != null)
        //            {
        //                parm.Add("pageSize", pageInfo.PageSize);
        //                parm.Add("pageNumber", pageInfo.PageNumber);
        //                parm.Add("orderString", pageInfo.OrderString);
        //                parm.Add("recordTotal", 0, DbType.Int32, ParameterDirection.Output);
        //            }
        //            if (commandParameters != null)
        //            {
        //                foreach (DictionaryEntry para in commandParameters)
        //                {
        //                    parm.Add(para.Key.ToString(), para.Value);
        //                }
        //            }
        //            data.List = connection.Query<LeadListReturn>(spName, parm, commandType: CommandType.StoredProcedure).AsList();

        //            data.recordTotal = parm.Get<int>("@recordTotal");//返回总条数

        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        throw new Exception(e.Message);
        //    }
        //    finally
        //    {
        //        connection.Close();
        //    }
        //    return data;
        //}
        ///// <summary>
        ///// 分页-执行存储过程  订单合同
        ///// </summary>
        ///// <param name="pageInfo">分页参数</param>
        ///// <param name="spName">存储过程名称</param>
        ///// <param name="commandParameters">查询参数</param>
        ///// <returns></returns>
        //public ContractOrderListResponse ExecStoredContractOrderList<T>(PageInfo pageInfo, string spName, Hashtable commandParameters)
        //{

        //    ContractOrderListResponse data = new ContractOrderListResponse();
        //    try
        //    {
        //        using (connection)
        //        {
        //            connection.Open();
        //            DynamicParameters parm = new DynamicParameters();
        //            if (pageInfo != null)
        //            {
        //                parm.Add("pageSize", pageInfo.PageSize);
        //                parm.Add("pageNumber", pageInfo.PageNumber);
        //                parm.Add("orderString", pageInfo.OrderString);
        //                parm.Add("recordTotal", 0, DbType.Int32, ParameterDirection.Output);
        //            }
        //            if (commandParameters != null)
        //            {
        //                foreach (DictionaryEntry para in commandParameters)
        //                {
        //                    parm.Add(para.Key.ToString(), para.Value);
        //                }
        //            }
        //            data.List = connection.Query<ContractOrderListReturn>(spName, parm, commandType: CommandType.StoredProcedure).AsList();

        //            data.recordTotal = parm.Get<int>("@recordTotal");//返回总条数

        //        }
        //    }
        //    catch (Exception e)
        //    {
        //        throw new Exception(e.Message);
        //    }
        //    finally
        //    {
        //        connection.Close();
        //    }
        //    return data;
        //}
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="spName"></param>
        /// <param name="commandParameters"></param>
        /// <param name="paras"></param>
        /// <returns></returns>
        public DataTable ExecStoredProcedureGetDateTable(string spName, Hashtable commandParameters, params SqlParameter[] paras)//传多个参数
        {
            DataTable result = new DataTable();
            try
            {
                connection.Open();
                SqlCommand command = connection.CreateCommand();
                PrepareCommand(command, connection, (SqlTransaction)null, CommandType.StoredProcedure, spName, commandParameters);
                if (paras != null)
                {
                    foreach (var para in paras)
                    {
                        command.Parameters.Add(para);
                    }
                }
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    if (reader.HasRows)
                    {
                        result.Load(reader);
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                connection.Close();
            }
            return result;
        }
        private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, Hashtable commandParameters)
        {
            //if the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
                connection.Open();
            }
            command.Connection = connection;
            command.CommandText = commandText;
            if (transaction != null)
            {
                command.Transaction = transaction;
            }
            command.CommandType = commandType;
            command.CommandTimeout = connection.ConnectionTimeout;
            if (commandParameters != null)
            {
                foreach (var key in commandParameters.Keys)
                {
                    if (commandParameters[key] is Guid)
                    {
                        Guid id = (Guid)commandParameters[key];
                        command.Parameters.AddWithValue(key.ToString(), Guid.Empty.Equals(id) ? DBNull.Value : commandParameters[key]);
                    }
                    else
                    {
                        command.Parameters.AddWithValue(key.ToString(), commandParameters[key] == null ? DBNull.Value : commandParameters[key]);
                    }
                }
            }
            return;
        }
        /// <summary>
        /// 执行SQL语句操作数据库
        /// </summary>
        /// <param name="cmdText">sql语句</param>
        /// <returns>影响行数</returns>
        public int ExecuteNonQuery(string cmdText)
        {

            using (SqlConnection conn = new SqlConnection(connectionstring))
            {
                StringBuilder commandText = new StringBuilder(cmdText);
                SqlCommand cmd = new SqlCommand(commandText.ToString(), conn);
                try
                {
                    if (conn.State != System.Data.ConnectionState.Open)
                    {
                        conn.Open();
                    }
                    int val = cmd.ExecuteNonQuery();
                    conn.Close();
                    return val;
                }
                catch (Exception err)
                {
                    if (conn.State != System.Data.ConnectionState.Closed)
                    {
                        conn.Close();
                    }
                    throw new Exception(err.Message);
                }
            }

        }

        /// <summary>
        /// 用户是否有指定角色
        /// </summary>
        /// <param name="userId"></param>
        /// <param name="roleName"></param>
        /// <returns></returns>
        public bool UserExitRole(string userId, List<string> roleName)
        {
            string sql = string.Format(@"select distinct r.name 
                                        from SystemUserRoles sr left
                                        join Role r on sr.roleid = r.roleid
                                        where sr.SystemUserId = '{0}'", userId);

            DataTable dt = ExecuteDt(sql);
            foreach (DataRow item in dt.Rows)
            {
                if (roleName.Contains(item["name"].ToString()))
                {
                    return true;
                }
            }
            return false;
        }


        /// <summary>
        /// 事务
        /// </summary>
        public SqlTransaction Transaction
        {
            get { return transaction; }
            set { transaction = value; }
        }

        public object CrmService { get; internal set; }

        /// <summary>
        /// 开始事务
        /// </summary>
        public void BeginTrans()
        {
            connection = new SqlConnection(connectionstring);
            connection.Open();
            transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTrans()
        {
            transaction.Commit();
        }

        /// <summary>
        /// 事务回滚
        /// </summary>
        public void RollbackTrans()
        {
            transaction.Rollback();
        }

        /// <summary>
        /// 结束事务，关闭连接
        /// </summary>
        public void FinishTrans()
        {
            connection.Close();
            connection.Dispose();
        }
    }
}

